const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

async function setupCompleteDatabase() {
    console.log('=================================');
    console.log('趣味英语 - 完整数据库初始化');
    console.log('（包含存储过程）');
    console.log('=================================\n');

    const config = {
        host: 'localhost',
        user: 'root',
        password: 'Lycc.2025',
        charset: 'utf8mb4',
        multipleStatements: true
    };

    let connection;

    try {
        console.log('步骤1: 连接MySQL...');
        connection = await mysql.createConnection(config);
        console.log('✅ 连接成功\n');

        // 创建数据库
        console.log('步骤2: 创建数据库...');
        await connection.query('CREATE DATABASE IF NOT EXISTS fun_english_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
        await connection.query('USE fun_english_db');
        console.log('✅ 数据库已创建\n');

        // 执行表结构SQL
        console.log('步骤3: 创建数据表...');
        const initSqlPath = path.join(__dirname, 'init-complete.sql');
        const initSql = fs.readFileSync(initSqlPath, 'utf8');
        
        // 分割SQL语句并逐个执行
        const statements = initSql.split(';').filter(stmt => stmt.trim() && !stmt.trim().startsWith('--'));
        for (const statement of statements) {
            if (statement.trim()) {
                try {
                    await connection.query(statement);
                } catch (err) {
                    if (!err.message.includes('Duplicate entry')) {
                        throw err;
                    }
                }
            }
        }
        console.log('✅ 数据表创建成功\n');

        // 创建存储过程（需要特殊处理）
        console.log('步骤4: 创建存储过程...');
        
        // 删除已存在的存储过程
        await connection.query('DROP PROCEDURE IF EXISTS update_daily_task');
        await connection.query('DROP PROCEDURE IF EXISTS calculate_next_review');
        
        // 创建存储过程1：更新每日任务
        const procedure1 = `
        CREATE PROCEDURE update_daily_task(
            IN p_user_id INT,
            IN p_task_type VARCHAR(50)
        )
        BEGIN
            DECLARE v_today DATE;
            SET v_today = CURDATE();
            
            IF NOT EXISTS (
                SELECT 1 FROM daily_tasks 
                WHERE user_id = p_user_id AND task_date = v_today AND task_type = p_task_type
            ) THEN
                INSERT INTO daily_tasks (user_id, task_date, task_type, target_count, stars_reward)
                VALUES (
                    p_user_id, 
                    v_today, 
                    p_task_type,
                    CASE p_task_type
                        WHEN 'login' THEN 1
                        WHEN 'learn_words' THEN 10
                        WHEN 'review' THEN 5
                        WHEN 'play_game' THEN 3
                        WHEN 'feed_pet' THEN 1
                    END,
                    CASE p_task_type
                        WHEN 'login' THEN 5
                        WHEN 'learn_words' THEN 10
                        WHEN 'review' THEN 8
                        WHEN 'play_game' THEN 10
                        WHEN 'feed_pet' THEN 3
                    END
                );
            END IF;
            
            UPDATE daily_tasks 
            SET current_count = current_count + 1,
                completed = (current_count + 1 >= target_count),
                completed_at = IF(current_count + 1 >= target_count, NOW(), completed_at)
            WHERE user_id = p_user_id AND task_date = v_today AND task_type = p_task_type;
            
            UPDATE users u
            INNER JOIN daily_tasks dt ON u.id = dt.user_id
            SET u.stars = u.stars + dt.stars_reward
            WHERE u.id = p_user_id 
                AND dt.task_date = v_today 
                AND dt.task_type = p_task_type 
                AND dt.completed = TRUE
                AND dt.completed_at > DATE_SUB(NOW(), INTERVAL 1 SECOND);
        END
        `;
        
        await connection.query(procedure1);
        console.log('  ✓ update_daily_task 创建成功');
        
        // 创建存储过程2：计算下次复习时间
        const procedure2 = `
        CREATE PROCEDURE calculate_next_review(
            IN p_user_id INT,
            IN p_word_id INT,
            IN p_is_correct BOOLEAN
        )
        BEGIN
            DECLARE v_review_count INT DEFAULT 0;
            DECLARE v_mastery_level INT DEFAULT 0;
            DECLARE v_next_interval INT;
            
            SELECT IFNULL(review_count, 0), IFNULL(mastery_level, 0) 
            INTO v_review_count, v_mastery_level
            FROM user_progress
            WHERE user_id = p_user_id AND word_id = p_word_id;
            
            IF p_is_correct THEN
                SET v_mastery_level = LEAST(100, v_mastery_level + 10);
                SET v_next_interval = CASE v_review_count
                    WHEN 0 THEN 5
                    WHEN 1 THEN 30
                    WHEN 2 THEN 720
                    WHEN 3 THEN 1440
                    WHEN 4 THEN 2880
                    WHEN 5 THEN 5760
                    WHEN 6 THEN 10080
                    ELSE 21600
                END;
            ELSE
                SET v_mastery_level = GREATEST(0, v_mastery_level - 5);
                SET v_next_interval = 5;
            END IF;
            
            UPDATE user_progress
            SET 
                review_count = v_review_count + 1,
                mastery_level = v_mastery_level,
                last_reviewed = NOW(),
                next_review = DATE_ADD(NOW(), INTERVAL v_next_interval MINUTE),
                correct_count = correct_count + IF(p_is_correct, 1, 0),
                wrong_count = wrong_count + IF(p_is_correct, 0, 1)
            WHERE user_id = p_user_id AND word_id = p_word_id;
        END
        `;
        
        await connection.query(procedure2);
        console.log('  ✓ calculate_next_review 创建成功');
        console.log('✅ 存储过程创建完成\n');

        // 检查创建的表
        const [tables] = await connection.query('SHOW TABLES');
        console.log('=================================');
        console.log('已创建的数据表:');
        console.log('=================================');
        tables.forEach(table => {
            console.log('  ✓', Object.values(table)[0]);
        });

        // 检查存储过程
        const [procedures] = await connection.query("SHOW PROCEDURE STATUS WHERE Db = 'fun_english_db'");
        console.log('\n已创建的存储过程:');
        procedures.forEach(proc => {
            console.log('  ✓', proc.Name);
        });

        console.log('\n=================================');
        console.log('✅ 完整数据库初始化成功！');
        console.log('=================================');
        console.log('\n下一步操作：');
        console.log('1. 运行: node vocabulary-import-fixed.js  (导入单词)');
        console.log('2. 运行: node create-admin.js  (创建管理员)');
        console.log('3. 启动应用测试\n');

    } catch (error) {
        console.error('❌ 错误:', error.message);
        if (error.code === 'ER_ACCESS_DENIED_ERROR') {
            console.log('\n💡 提示: MySQL密码可能不正确');
            console.log('请修改 database/setup-complete.js 文件第12行的密码\n');
        }
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('数据库连接已关闭\n');
        }
    }
}

setupCompleteDatabase();

